package com.mymaxis.service;import com.myebills.ebpsfrontend.controller.CusConnectionPoolServlet;import com.mymaxis.ebppx.EbppxAcctDetailsDataBean;import com.mymaxis.ebppx.EbppxConstants;import com.mymaxis.ebppx.EbppxUtil;import com.mymaxis.ebppx.MyMaxisAppConnectionPoolServlet;import com.mymaxis.manager.DBConnectionManager;import java.sql.*;import java.text.DateFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;public class AcctDetails{	private String strAcctId = "";	public AcctDetails(){	}	private static boolean connectionPoolOn = false;    public static Connection getDbConnection() throws Exception {         Connection conn=null;        try {			com.checkfree.isolutions.property.Properties appCfg = com.checkfree.isolutions.property.PropertiesConfig.getApplicationSettings();			if (appCfg.hasProperty("MyMaxisAppConnectionPool") == false){								connectionPoolOn = false;							}else {				String temp = appCfg.getProperty("MyMaxisAppConnectionPool");				if (temp.compareToIgnoreCase("Yes")==0){					connectionPoolOn = true;				}			}			if (connectionPoolOn){                conn = MyMaxisAppConnectionPoolServlet.connectionPool.getConnection();                //Sytem.out.println(MyMaxisAppConnectionPoolServlet.connectionPool.toString());                //Sytem.out.println("---MymaxisApp Connection opened connectionpool---");			} else {				String jdbcDriver = new String();				String jdbcDataSource = new String();				String username = new String();				String password = new String();				if (appCfg.hasProperty("JDBCDriverName") == false)					throw new IllegalArgumentException("JDBC Driver not specified!");				else					jdbcDriver = appCfg.getProperty("JDBCDriverName");				if (appCfg.hasProperty("JDBCDataSourceName") == false)					throw new IllegalArgumentException("JDBC Data Source not specified!");				else					jdbcDataSource = appCfg.getProperty("JDBCDataSourceName");				if (appCfg.hasProperty("DataBaseUserid") == false)					throw new IllegalArgumentException("Database userid not specified!");				else					username = appCfg.getProperty("DataBaseUserid");				if (appCfg.hasProperty("DataBasePassword") == false)					throw new IllegalArgumentException("Database password not specified!");				else					password = appCfg.getProperty("DataBasePassword");				Class.forName(jdbcDriver);				conn = DriverManager.getConnection(jdbcDataSource, username, password);				//Sytem.out.println("--- MyMaxisApp Connection opened ---");			}        }        catch(Exception e) {			if (connectionPoolOn) {				e.printStackTrace();            	throw new Exception("Error getting DB connection. Connection pool servlet should be running: " + e);			}			else {            	throw new Exception("Error getting DB connection: " + e);			}        }        return conn;    }        public static void freeDbConnection(Connection conn) throws Exception {        try {            if (connectionPoolOn){                MyMaxisAppConnectionPoolServlet.connectionPool.free(conn);                //Sytem.out.println("--- MyMaxisApp Connection closed ---");                //Sytem.out.println(MyMaxisAppConnectionPoolServlet.connectionPool.toString());            } else {                if (conn != null){                    conn.close();                    //Sytem.out.println("--- MyMaxisApp Connection closed ---");                } else {                    //Sytem.out.println("--- MyMaxisApp Connection is null ---");                }            }        }        catch(Exception e) {            throw new Exception("Error closing MyMaxisApp DB connection : " + e);        }   }/**********************************************************************************************/	public AcctDetails(String acctId){		this.strAcctId = acctId;	}    /***************************************************************************/    public static void insertAcctDetails(String acctId,String acctKey,String custGroup,String supIndicator) throws Exception {        Connection connection = null;        try {			connection = DBConnectionManager.getDbConnection();            if(connection!=null){				insertAcctDetails(connection, acctId, acctKey, custGroup, supIndicator);            }            DBConnectionManager.freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging inserting account details table : " + e);        }finally{        	if(connection != null){        		DBConnectionManager.freeDbConnection(connection);        	}        }    }    public static void insertAcctDetails(Connection connection, String acctId,String acctKey,String custGroup,String supIndicator) throws Exception {           	PreparedStatement pstmt = null;    	try {            if(connection!=null){                pstmt = connection.prepareStatement("INSERT INTO ACCOUNTDETAILS (ACCTID, ACCTKEY, CUSTGROUP, SUPINDICATOR) VALUES (?,?,?,?)");                pstmt.setString(1,acctId);                pstmt.setString(2,acctKey);                pstmt.setString(3,custGroup);                pstmt.setString(4,supIndicator);                pstmt.execute();				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging inserting account details table : " + e);        }finally{        	if(pstmt != null){        		pstmt.close();        	}        }    }/***************************************************************************//*    public static void updateAcctDetails(String acctId,String acctKey,String custGroup,String supIndicator) throws Exception {        Connection connection = null;        try {			connection = getDbConnection();            if(connection!=null){				updateAcctDetails(connection, acctId, acctKey, custGroup, supIndicator);            }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging updating account details table : " + e);        }    }*/        public static void updateAcctDetails(Connection connection, String acctId,String acctKey,String custGroup,String supIndicator) throws Exception {    	PreparedStatement pstmt = null;        try {            if(connection!=null){                pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET ACCTKEY = ?,CUSTGROUP = ?,SUPINDICATOR = ? WHERE ACCTID = ?");                pstmt.setString(1,acctKey);                pstmt.setString(2,custGroup);                pstmt.setString(3,supIndicator);                pstmt.setString(4,acctId);                pstmt.executeUpdate();				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table : " + e);        }finally{        	if(pstmt != null){        		pstmt.close();        	}        }    }   /* public static boolean updateAllAcctDetailsCsr(String acctId,String acctSubscription, String emailDeliverySubscription, String smsSubscription, String emailAdd, String smsNo, String strDate, String strLastModifyDate,String updateByUser,String updateByChannel) throws Exception {        Connection connection = null;        boolean result = false;        try {			connection = getDbConnection();			            if(connection!=null){            	 result =updateAllAcctDetailsCsr(connection,acctId, acctSubscription,emailDeliverySubscription,smsSubscription,emailAdd,smsNo,strDate, strLastModifyDate,updateByUser,updateByChannel);            	             }                        freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging updating account details table : " + e);        }        return result;    }*/    public static boolean updateAllAcctDetailsCsr(Connection connection,String acctId,String emailDeliverySubscription, String smsSubscription, String emailAdd, String smsNo, String strDate, String strLastModifyDate,String updateByUser,String updateByChannel) throws Exception {    	        boolean result=false;        PreparedStatement pstmt = null;        try {            //Sytem.out.println("acctId :"+acctId);            //Sytem.out.println("emailDeliverySubscription :"+emailDeliverySubscription);            //Sytem.out.println("smsSubscription :"+smsSubscription);            //Sytem.out.println("smsNo :"+smsNo);            //Sytem.out.println("emailAdd :"+emailAdd);            //Sytem.out.println("emailAdd :"+strDate);            //Sytem.out.println("strDate :"+strDate);            //Sytem.out.println("strLastModifyDate :"+strLastModifyDate);            //Sytem.out.println("UpdateByUser :"+updateByUser);            //Sytem.out.println("UpdateByChannel :"+updateByChannel);            int updateCount;                        if(connection!=null){            	            	final DateFormat df = new SimpleDateFormat(EbppxConstants.DATE_FORMAT+" "+EbppxConstants.TIME_FORMAT);                java.sql.Timestamp registerDate = null;                if (!strDate.equals("")){                	registerDate = new Timestamp((df.parse(strDate)).getTime());                }                                java.sql.Timestamp modifyDate = null;                if (!strLastModifyDate.equals("")){                	modifyDate = new Timestamp((df.parse(strLastModifyDate)).getTime());                }                                //Sytem.out.println("smsSubscriptionFlag :"+smsSubscription);                if(!smsSubscription.equalsIgnoreCase("")){                	                    //Sytem.out.println("not sms subscription");                    pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET EMAILDELIVERYSUBSCRIPTION = ?,SMSSUBSCRIPTION = ?,EMAILADD = ?,SMSNO = ?,REGISTER_DATE = ?,LASTMODIFYDATE = ?,UPDATEDBYCHANNEL = ?,UPDATEDBYUSER = ? WHERE ACCTID = ?");                	pstmt.setString(1,emailDeliverySubscription);                	pstmt.setString(2,smsSubscription);                	pstmt.setString(3,emailAdd);                	pstmt.setString(4,smsNo);                	pstmt.setTimestamp(5, registerDate);                	pstmt.setTimestamp(6, modifyDate);                	pstmt.setString(7, updateByChannel);                	pstmt.setString(8, updateByUser);                	pstmt.setString(9, acctId);                	pstmt.executeUpdate();                	updateCount = pstmt.getUpdateCount();                                }else{                	 //Sytem.out.println("sms subscription");                	 pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET EMAILDELIVERYSUBSCRIPTION = ?,EMAILADD = ?,REGISTER_DATE = ?,LASTMODIFYDATE = ?,UPDATEDBYCHANNEL = ?,UPDATEDBYUSER = ? WHERE ACCTID = ?");                                        pstmt.setString(1,emailDeliverySubscription);                     pstmt.setString(2,emailAdd);                     pstmt.setTimestamp(3, registerDate);                     pstmt.setTimestamp(4, modifyDate);                     pstmt.setString(5, updateByChannel);                     pstmt.setString(6, updateByUser);                     pstmt.setString(7, acctId);                     pstmt.executeUpdate();	                     updateCount = pstmt.getUpdateCount();                	                	                }                				//Sytem.out.println("updateCount:" + updateCount);								if (updateCount != 0) {										result = true;									}				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {        	e.printStackTrace();            throw new Exception("Error logging updating account details table : " + e);        }finally{        	        	if (pstmt != null) {				pstmt.close();			}        }                return result;    }    /*//-----------------------------------Setter program--------------------------------------------------//    public static void updateOldAcctKey(String acctId,String acctKey) throws Exception {        Connection connection = null;        try {			connection = getDbConnection();            if(connection!=null){				updateOldAcctKey(connection, acctId, acctKey);            }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for old account key: " + e);        }    }*/    public static void updateOldAcctKey(Connection connection, String acctId,String acctKey) throws Exception {    	PreparedStatement pstmt = null;        try {            if(connection!=null){                pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET ACCTKEY = ? WHERE ACCTID = ?");                pstmt.setString(1,acctKey);                pstmt.setString(2,acctId);                pstmt.executeUpdate();				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for old account key: " + e);        }finally{        	if (pstmt != null) {				pstmt.close();			}        }    }     public static void updateCustGroup(Connection connection, String acctId,String custGroup) throws Exception {        try {            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET CUSTGROUP = ? WHERE ACCTID = ?");                pstmt.setString(1,custGroup);                pstmt.setString(2,acctId);                pstmt.executeUpdate();				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for cust group: " + e);        }    }/*    public static void updateEmailAdd(String acctId,String emailAdd) throws Exception {        Connection connection = null;        try {			connection = getDbConnection();            if(connection!=null){				updateEmailAdd(connection, acctId, emailAdd);            }            freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for email address: " + e);        }    }*/    public static void updateEmailAdd(Connection connection, String acctId,String emailAdd) throws Exception {        try {            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET EMAILADD = ? WHERE ACCTID = ?");                pstmt.setString(1,emailAdd);                pstmt.setString(2,acctId);                pstmt.executeUpdate();				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for email address: " + e);        }    }      public static void updateSMSNo(Connection connection, String acctId,String smsNo) throws Exception {        try {            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET SMSNO = ? WHERE ACCTID = ?");                pstmt.setString(1,smsNo);                pstmt.setString(2,acctId);                pstmt.executeUpdate();				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for sms no: " + e);        }    }      public static void updateAcctSubscription(Connection connection, String acctId,String acctSubscription) throws Exception {        try {            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET ACCTSUBSCRIPTION = ? WHERE ACCTID = ?");                pstmt.setString(1,acctSubscription);                pstmt.setString(2,acctId);                pstmt.executeUpdate();				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for account subscription: " + e);        }    }    public static void updateEmailDeliverySubscription(String acctId,String emailDeliverySubscription) throws Exception {        Connection connection = null;        try {			connection = DBConnectionManager.getDbConnection();            if(connection!=null){            	updateEmailDeliverySubscription(connection, acctId, emailDeliverySubscription);            }            DBConnectionManager.freeDbConnection(connection);        }        catch(Exception e) {            throw new Exception("Error logging inserting account details table : " + e);        }    }    public static void updateEmailDeliverySubscription(Connection connection, String acctId,String emailDeliverySubscription) throws Exception {        try {            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET EMAILDELIVERYSUBSCRIPTION = ? WHERE ACCTID = ?");                pstmt.setString(1,emailDeliverySubscription);                pstmt.setString(2,acctId);                pstmt.executeUpdate();				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for email delivery subscription: " + e);        }    }      public static void updateSMSSubscription(Connection connection, String acctId,String smsSubscription) throws Exception {        try {            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET SMSSUBSCRIPTION = ? WHERE ACCTID = ?");                pstmt.setString(1,smsSubscription);                pstmt.setString(2,acctId);                pstmt.executeUpdate();				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for sms subscription: " + e);        }    }    public static void updateLastViewedDate(Connection connection, String acctId,String lastViewedDate) throws Exception {        try {            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET LASTVIEWEDDATE = ? WHERE ACCTID = ?");                pstmt.setString(1,lastViewedDate);                pstmt.setString(2,acctId);                pstmt.executeUpdate();				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for last viewed date: " + e);        }    }    public static void updateSupIndicator(Connection connection, String acctId,String supIndicator) throws Exception {        try {            if(connection!=null){                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET SUPINDICATOR = ? WHERE ACCTID = ?");                pstmt.setString(1,supIndicator);                pstmt.setString(2,acctId);                pstmt.executeUpdate();				if (pstmt != null) {					pstmt.close();				}            }        }        catch(Exception e) {            throw new Exception("Error logging updating account details table for sup indicator: " + e);        }    }////-----------------------------------------Getter program---------------------------------------------////	/**			To retrieve acctid regardless user input is new/old account no.	*///////////////////////////////////////////////////////////////////////////////////////    public static EbppxAcctDetailsDataBean getAcctDetails(Connection connection,String acctNo)throws Exception {    	        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        EbppxAcctDetailsDataBean acctDataBean = new EbppxAcctDetailsDataBean();        String strNewAccountKey = "",strOldAccountKey="",strCustGroup="",strEmailAdd="",strSMSNo="",        	   strAcctSubscription="",strEmailDeliverySubscription="",strSMSSubscription,strSupIndicator="";        Date dateRegisteredDate=null;                try{			if (connection!=null){				//search for status records first				query.append("SELECT TRIM(ACCTID),TRIM(ACCTKEY),TRIM(CUSTGROUP),TRIM(EMAILADD),TRIM(SMSNO),TRIM(EMAILDELIVERYSUBSCRIPTION),TRIM(SMSSUBSCRIPTION),TRIM(SUPINDICATOR),REGISTER_DATE, LASTMODIFYDATE,UPDATEDBYCHANNEL,UPDATEDBYUSER FROM ACCOUNTDETAILS WHERE ACCTID = ?");				// prepare the statement and bind parameter				pstmt = connection.prepareStatement(query.toString());				pstmt.setString(1,acctNo);				//pstmt.setString(2,acctNo);				rs   = pstmt.executeQuery();      				if(rs.next()){					//Sytem.out.println("In account details get details.....");					acctDataBean.setAcctId(rs.getString(1));//strNewAccountKey					acctDataBean.setOldAcctKey(rs.getString(2));//strOldAccountKey					acctDataBean.setCustGroup(rs.getString(3));//strCustGroup					acctDataBean.setEmailAdd(rs.getString(4));//strEmailAdd					acctDataBean.setSMSNo(rs.getString(5));//strSMSNo					acctDataBean.setEmailSubscription(rs.getString(6));//strEmailDeliverySubscription					acctDataBean.setSMSSubscription(rs.getString(7));//strSMSSubscription					acctDataBean.setSupIndicator(rs.getString(8));//strSupIndicator								//	dateRegisteredDate = rs.getTimestamp(10);									acctDataBean.setRegisterDate(rs.getTimestamp(9));//dateRegisterDate					acctDataBean.setLastModifyDate(rs.getTimestamp(10));//dateLastModifyDate					acctDataBean.setUpdatedByChannel(rs.getString(11));					acctDataBean.setUpdatedByUser(rs.getString(12));					acctDataBean.setExceptionMsg("");					//Sytem.out.println("End account details get details.....");				}				if (pstmt != null) {					pstmt.close();				}			}			//Sytem.out.println("Retrieve acctdetails regardless user input is new/old account no. has been successfully executed");		}catch(Exception e){			throw new Exception("" + e);		}finally{			if(rs!=null){				rs.close();			}			if(pstmt != null){				pstmt.close();			}		}		return acctDataBean;	}    public static EbppxAcctDetailsDataBean getAcctDetails(String acctNo)throws Exception {		Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        EbppxAcctDetailsDataBean acctDataBean = new EbppxAcctDetailsDataBean();        try{			connection = DBConnectionManager.getDbConnection();			if (connection!=null){				acctDataBean = getAcctDetails(connection,acctNo);			}            //Sytem.out.println("Retrieve acctdetails regardless user input is new/old account no. has been successfully executed");		}catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){               rs.close();            }            if(connection!=null){            	DBConnectionManager.freeDbConnection(connection);            }        }        return acctDataBean;    }////////////////////////////////////////////////////////////////////////////////////////////////////	public static String getAcctId(Connection connection,String acctNo)throws Exception {        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strNewAccountKey = "";        try{			if (connection!=null){				//search for status records first				query.append("SELECT TRIM(ACCTID) FROM ACCOUNTDETAILS WHERE ACCTID = ? OR ACCTKEY = ?");				// prepare the statement and bind parameter				pstmt = connection.prepareStatement(query.toString());				pstmt.setString(1,acctNo);				pstmt.setString(2,acctNo);				rs   = pstmt.executeQuery();				if(rs.next()){					strNewAccountKey = rs.getString(1);				}				if (pstmt != null) {					pstmt.close();				}			}			//Sytem.out.println("Retrieve acctid regardless user input is new/old account no. has been successfully executed");		}catch(Exception e){			throw new Exception("" + e);		}finally{			if(rs!=null){				rs.close();			}		}        if (strNewAccountKey == null){			strNewAccountKey = "";		}		return strNewAccountKey;	}    public static String getAcctId(String acctNo)throws Exception {		Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strNewAccountKey = "";        try{			connection =DBConnectionManager.getDbConnection();			if (connection!=null){				strNewAccountKey = getAcctId(connection,acctNo);			}            //Sytem.out.println("Retrieve acctid regardless user input is new/old account no. has been successfully executed");		}catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }            if(connection!=null){            	DBConnectionManager.freeDbConnection(connection);            }        }        return strNewAccountKey;    }    public static String getAcctKey(Connection connection,String acctNo)throws Exception {        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strOldAccountKey = "";        try{                if (connection!=null){                    //search for status records first                    query.append("SELECT TRIM(ACCTKEY) FROM ACCOUNTDETAILS WHERE ACCTID = ? OR ACCTKEY = ?");                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctNo);                    pstmt.setString(2,acctNo);                    rs   = pstmt.executeQuery();					if(rs.next()){						strOldAccountKey = rs.getString(1);					}                    					if(rs != null){						rs.close();					}                    if (pstmt != null) {                        pstmt.close();                    }                }            //Sytem.out.println("Retrieve acctKey regardless user input is new/old account no. has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs.close();            }            if(pstmt != null){            	pstmt.close();            }        }        if (strOldAccountKey == null){			strOldAccountKey = "";		}        return strOldAccountKey;    }    public static String getAcctKey(String acctNo)throws Exception {        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strOldAccountKey = "";        try{            connection = DBConnectionManager.getDbConnection();            if (connection!=null){				strOldAccountKey = getAcctKey(connection,acctNo);            }            //Sytem.out.println("Retrieve acctKey regardless user input is new/old account no. has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs.close();            }            if(connection!=null){            	DBConnectionManager.freeDbConnection(connection);            }        }        return strOldAccountKey;    }    public static String getOldAccountKey(Connection connection,String acctId)throws Exception {        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strOldAccountKey = "";        try{                if (connection!=null){                    //search for status records first                    query.append("SELECT TRIM(ACCTKEY) FROM ACCOUNTDETAILS WHERE ACCTID = ?");                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);                    rs   = pstmt.executeQuery();					if(rs.next()){						strOldAccountKey = rs.getString(1);					}                    if (pstmt != null) {                        pstmt.close();                    }                }            //Sytem.out.println("Retrieve Old Account key with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){            	                rs=null;            }        }        if (strOldAccountKey == null){			strOldAccountKey = "";		}        return strOldAccountKey;    }    public static String getOldAccountKey(String acctId)throws Exception {        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strOldAccountKey = "";        try{            connection = DBConnectionManager.getDbConnection();                if (connection!=null){					strOldAccountKey = getOldAccountKey(connection,acctId);                }            //Sytem.out.println("Retrieve Old Account key with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }            if(connection!=null){            	DBConnectionManager.freeDbConnection(connection);            }        }        return strOldAccountKey;    }    public static String getCustGroup(Connection connection,String acctId)throws Exception {        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strCustGroup = "";        try{                if (connection!=null){                    //search for status records first                    query.append("SELECT TRIM(CUSTGROUP) FROM ACCOUNTDETAILS WHERE ACCTID = ?");                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);                    rs   = pstmt.executeQuery();					if(rs.next()){						strCustGroup = rs.getString(1);					}                    if (pstmt != null) {                        pstmt.close();                    }                }            //Sytem.out.println("Retrieve custgroup with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }        }        if (strCustGroup == null){			strCustGroup = "";		}        return strCustGroup;    }    public static String getCustGroup(String acctId)throws Exception {        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strCustGroup = "";        try{            connection = DBConnectionManager.getDbConnection();                if (connection!=null){					strCustGroup = getCustGroup(connection, acctId);                }            //Sytem.out.println("Retrieve custgroup with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }            if(connection!=null){            	DBConnectionManager.freeDbConnection(connection);            }        }        return strCustGroup;    }    public static String getEmailAdd(Connection connection,String acctId)throws Exception {        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strEmailAdd = "";        try{                if (connection!=null){                    //search for status records first                    query.append("SELECT TRIM(EMAILADD) FROM ACCOUNTDETAILS WHERE ACCTID = ? OR ACCTKEY = ?");                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);                    pstmt.setString(2,acctId);                    rs   = pstmt.executeQuery();					if(rs.next()){						strEmailAdd = rs.getString(1);					}                    if (pstmt != null) {                        pstmt.close();                    }                }            //Sytem.out.println("Retrieve email address with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }        }        if (strEmailAdd == null){			strEmailAdd = "";		}        return strEmailAdd;    }    public static String getEmailAdd(String acctId)throws Exception {        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strEmailAdd = "";        try{            connection = DBConnectionManager.getDbConnection();                if (connection!=null){					strEmailAdd = getEmailAdd(connection, acctId);                }            //Sytem.out.println("Retrieve email address with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }            if(connection!=null){            	DBConnectionManager.freeDbConnection(connection);            }        }        return strEmailAdd;    }    public static String getSMSNo(Connection connection,String acctId)throws Exception {        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strSMSNo = "";        try{                if (connection!=null){                    //search for status records first                    query.append("SELECT TRIM(SMSNO) FROM ACCOUNTDETAILS WHERE ACCTID = ?");                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);                    rs   = pstmt.executeQuery();					if(rs.next()){						strSMSNo = rs.getString(1);					}					if(rs != null){						rs.close();					}                    if (pstmt != null) {                        pstmt.close();                    }                }            //Sytem.out.println("Retrieve SMS no with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs != null){                rs.close();            }            if(pstmt != null){            	pstmt.close();            }        }        if (strSMSNo == null){			strSMSNo = "";		}        return strSMSNo;    }    public static String getSMSNo(String acctId)throws Exception {        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strSMSNo = "";        try{            connection = DBConnectionManager.getDbConnection();                if (connection!=null){					strSMSNo = getSMSNo(connection, acctId);                }            //Sytem.out.println("Retrieve SMS no with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }            if(connection!=null){            	DBConnectionManager.freeDbConnection(connection);            }        }        return strSMSNo;    }    public static String getAcctSubscription(Connection connection,String acctId)throws Exception {        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strAcctSubscription = "";        try{                if (connection!=null){                    //search for status records first                    query.append("SELECT TRIM(ACCTSUBSCRIPTION) FROM ACCOUNTDETAILS WHERE ACCTID = ?");                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);                    rs   = pstmt.executeQuery();					if(rs.next()){						strAcctSubscription = rs.getString(1);					}                    if (pstmt != null) {                        pstmt.close();                    }                }            //Sytem.out.println("Retrieve account subscription with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }        }        if (strAcctSubscription == null){			strAcctSubscription = "";		}        return strAcctSubscription;    }    public static String getAcctSubscription(String acctId)throws Exception {        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strAcctSubscription = "";        try{            connection = DBConnectionManager.getDbConnection();                if (connection!=null){					strAcctSubscription = getAcctSubscription(connection, acctId);                }            //Sytem.out.println("Retrieve account subscription with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }            if(connection!=null){            	DBConnectionManager.freeDbConnection(connection);            }        }        return strAcctSubscription;    }    public static String getEmaiDeliverySubscription(Connection connection,String acctId)throws Exception {        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strEmailDeliverySubscription = "";        try{                if (connection!=null){                    //search for status records first                    query.append("SELECT TRIM(EMAILDELIVERYSUBSCRIPTION) FROM ACCOUNTDETAILS WHERE ACCTID = ?");                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);                    rs   = pstmt.executeQuery();					if(rs.next()){						strEmailDeliverySubscription = rs.getString(1);					}                    if (pstmt != null) {                        pstmt.close();                    }                }            //Sytem.out.println("Retrieve email delivery subscription with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }        }        if (strEmailDeliverySubscription == null){			strEmailDeliverySubscription = "";		}        return strEmailDeliverySubscription;    }    public static String getEmaiDeliverySubscription(String acctId)throws Exception {        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strEmailDeliverySubscription = "";        try{            connection = DBConnectionManager.getDbConnection();                if (connection!=null){					strEmailDeliverySubscription = getEmaiDeliverySubscription(connection, acctId);                }            //Sytem.out.println("Retrieve email delivery subscription with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }            if(connection!=null){            	DBConnectionManager.freeDbConnection(connection);            }        }        return strEmailDeliverySubscription;    }    public static String getSMSSubscription(Connection connection,String acctId)throws Exception {        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strSMSSubscription = "";        try{                if (connection!=null){                    //search for status records first                    query.append("SELECT TRIM(SMSSUBSCRIPTION) FROM ACCOUNTDETAILS WHERE ACCTID = ?");                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);                    rs   = pstmt.executeQuery();					if(rs.next()){						strSMSSubscription = rs.getString(1);					}					if(rs != null){						rs.close();					}                    if (pstmt != null) {                        pstmt.close();                    }                }            //Sytem.out.println("Retrieve SMS subscription with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs != null){               rs.close();            }            if(pstmt != null){            	pstmt.close();            }        }        if (strSMSSubscription == null){			strSMSSubscription = "";		}        return strSMSSubscription;    }    public static String getSMSSubscription(String acctId)throws Exception {        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strSMSSubscription = "";        try{            connection = DBConnectionManager.getDbConnection();                if (connection!=null){					strSMSSubscription = getSMSSubscription(connection, acctId);                }            //Sytem.out.println("Retrieve SMS subscription with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }            if(connection!=null){            	DBConnectionManager.freeDbConnection(connection);            }        }        return strSMSSubscription;    }    public static String getSupIndicator(Connection connection,String acctId)throws Exception {        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strSupIndicator= "";        try{                if (connection!=null){                    //search for status records first                    query.append("SELECT TRIM(SUPINDICATOR) FROM ACCOUNTDETAILS WHERE ACCTID = ? OR ACCTKEY= ?");                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);                    pstmt.setString(2,acctId);                    rs   = pstmt.executeQuery();					if(rs.next()){						strSupIndicator = rs.getString(1);					}                    if (pstmt != null) {                        pstmt.close();                    }                }            //Sytem.out.println("Retrieve last viewed date with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }        }        if (strSupIndicator == null){			strSupIndicator = "";		}        return strSupIndicator;    }    public static String getSupIndicator(String acctId)throws Exception {        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strSupIndicator = "";        try{            connection = DBConnectionManager.getDbConnection();                if (connection!=null){					strSupIndicator = getSupIndicator(connection, acctId);                }            //Sytem.out.println("Retrieve last viewed date with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }            if(connection!=null){            	DBConnectionManager.freeDbConnection(connection);            }        }        return strSupIndicator;    }    public static String getCorpCust(Connection connection,String acctId)throws Exception {        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strCorpCust= "";        try{		//Sytem.out.println("COme in sencond");                if (connection!=null){                    //search for status records first                    query.append("SELECT TRIM(CORPCUST) FROM ACCOUNTDETAILS WHERE ACCTID = '"+acctId+"' ");                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    rs   = pstmt.executeQuery();					if(rs.next()){						strCorpCust = rs.getString(1);					}                    if (pstmt != null) {                        pstmt.close();                    }                }            //Sytem.out.println("Retrieve corporate customer with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }        }		//Sytem.out.println("COme in third"+strCorpCust);        return strCorpCust;    }  /*  public static String getCorpCust(String acctId)throws Exception {		//Sytem.out.println("COme in first");        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        String strCorpCust = "";        try{            connection = getDbConnection();                if (connection!=null){					strCorpCust = getCorpCust(connection, acctId);                }            //Sytem.out.println("Retrieve corporate customer with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }            if(connection!=null){                freeDbConnection(connection);            }        }		//Sytem.out.println("COme in fourth"+strCorpCust);        return strCorpCust;    }*/  /*  public static boolean checkIfExistInAcctDetails(String acctId)throws Exception {        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        int intResult = 0;		boolean boolResult = false;        try{            connection = getDbConnection();            if (connection!=null){				boolResult = checkIfExistInAcctDetails(connection, acctId);            }            //Sytem.out.println("Check if the record exist in account details has successfully been executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(connection!=null){                freeDbConnection(connection);            }            if(rs!=null){                rs=null;            }        }        return boolResult;    }*/    public static boolean isOldAccountKey(String acctNo)throws Exception {		boolean result = false;		try{			String strOldAccountKey = getAcctKey(acctNo);			result = (EbppxUtil.nullToEmpty(strOldAccountKey).equals("") || !(EbppxUtil.nullToEmpty(strOldAccountKey).equals(EbppxUtil.nullToEmpty(acctNo)))) ? false : true;			return result;		}catch(Exception e){			throw new Exception("" + e);		}	}    public static boolean checkIfExistInAcctDetails(Connection connection, String acctId)throws Exception {        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        int intResult = 0;		boolean boolResult = false;        try{            if (connection!=null){				//search for status records first				query.append("SELECT COUNT(ACCTID) FROM ACCOUNTDETAILS WHERE ACCTID= ?");                // prepare the statement and bind parameter                pstmt = connection.prepareStatement(query.toString());                pstmt.setString(1,acctId);                rs   = pstmt.executeQuery();				if(rs.next()){					intResult = rs.getInt(1);				}				if(intResult!=0){					boolResult = true;				}                if (pstmt != null) {                    pstmt.close();                }            }            //Sytem.out.println("Check if the record exist in account details has successfully been executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }        }        return boolResult;    }           public static Date getRegisterDate(Connection connection,String acctId)throws Exception {        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        Date dateRegisterDate = null;        try{                if (connection!=null){                    //search for status records first                    query.append("SELECT REGISTER_DATE FROM ACCOUNTDETAILS WHERE ACCTID = ? OR ACCTKEY = ?");                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);                    pstmt.setString(2,acctId);                    rs   = pstmt.executeQuery();					if(rs.next()){						dateRegisterDate = rs.getDate(1);					}                    if (pstmt != null) {                        pstmt.close();                    }                }            //Sytem.out.println("Retrieve email update date with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }        }        if (dateRegisterDate == null){        	dateRegisterDate = null;		}        return dateRegisterDate;    }    public static Date getRegisterDate(String acctId)throws Exception {        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        Date dateRegisterDate = null;        try{            connection = DBConnectionManager.getDbConnection();                if (connection!=null){                	dateRegisterDate = getRegisterDate(connection, acctId);                }            //Sytem.out.println("Retrieve email update date with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }            if(connection!=null){            	DBConnectionManager.freeDbConnection(connection);            }        }        return dateRegisterDate;    }        public static Date getLastModifyDate(Connection connection,String acctId)throws Exception {        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        Date dateLastModifyDate = null;        try{                if (connection!=null){                    //search for status records first                    query.append("SELECT LASTMODIFYDATE FROM ACCOUNTDETAILS WHERE ACCTID = ? OR ACCTKEY = ?");                    // prepare the statement and bind parameter                    pstmt = connection.prepareStatement(query.toString());                    pstmt.setString(1,acctId);                    pstmt.setString(2,acctId);                    rs   = pstmt.executeQuery();					if(rs.next()){						dateLastModifyDate = rs.getDate(1);					}                    if (pstmt != null) {                        pstmt.close();                    }                }            //Sytem.out.println("Retrieve email update date with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }        }        if (dateLastModifyDate == null){        	dateLastModifyDate = null;		}        return dateLastModifyDate;    }    public static Date getLastModifyDate(String acctId)throws Exception {        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();        Date dateLastModifyDate = null;        try{            connection = DBConnectionManager.getDbConnection();                if (connection!=null){                	dateLastModifyDate = getLastModifyDate(connection, acctId);                }            //Sytem.out.println("Retrieve email update date with account ID has been successfully executed");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }            if(connection!=null){            	DBConnectionManager.freeDbConnection(connection);            }        }        return dateLastModifyDate;    }      //=================added by tzer kang        public static void logEmailDelivery(Connection connection,String acctId, String emailAddress, String date, String userId, String userField1, String userField2,String channelID) throws Exception {        try {        	//Sytem.out.println("TEST LOG");        	            if (connection!=null ){                StringBuffer stbSQL = new StringBuffer();                final DateFormat df = new SimpleDateFormat(EbppxConstants.DATE_FORMAT+" "+EbppxConstants.TIME_FORMAT);                java.sql.Timestamp ts1 = null;                java.sql.Timestamp ts2 = null;                if (!date.equals("")){                    ts1 = new Timestamp((df.parse(date)).getTime());                              PreparedStatement pstmt = connection.prepareStatement("INSERT INTO EBILLREGISTERLOG (ACCTID,EMAILADDR,DATE1,USERID,USERFIELD1, USERFIELD2, CHANNELID) VALUES (?,?,?,?,?,?,?)");                pstmt.setString(1,acctId);                pstmt.setString(2,emailAddress);                pstmt.setTimestamp(3,ts1);                pstmt.setString(4,userId);                pstmt.setString(5,userField1);                pstmt.setString(6,userField2);                pstmt.setString(7,channelID);                              pstmt.execute();  				if (pstmt != null) {  					pstmt.close();  				}            }            }        }        catch(Exception e) {            throw new Exception("Error logging user activity : " + e);        }        }    // ===END ADDED BY TZER KANG    /************************************************************************************************/    public static String getCategoryDatabase(Connection connection)throws Exception {        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();		String strCategoryDatabase = "";        try{			if (connection!=null){				//search for status records first                query.append("SELECT DS_DATABASE FROM SERVER_DEFINITION WHERE SERVER_TYPE=1");				// prepare the statement and bind parameter				pstmt = connection.prepareStatement(query.toString());                rs   = pstmt.executeQuery();				if(rs.next()){					strCategoryDatabase = rs.getString(1);				}				if (pstmt != null) {					pstmt.close();				}			}            //Sytem.out.println("Select category database name with connection has been successfully retrieved");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }        }        return strCategoryDatabase;    }////////////////////////////////////////////////////////////////////////////////////////////////    public static String getCategoryDatabase()throws Exception {        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();		String strCategoryDatabase = "";        try{			connection = DBConnectionManager.getKenanDbConnection();			if (connection!=null){				//search for status records first                query.append("SELECT DS_DATABASE FROM SERVER_DEFINITION WHERE SERVER_CATEGORY=1");				//Sytem.out.println(query.toString());				// prepare the statement and bind parameter				pstmt = connection.prepareStatement(query.toString());                rs   = pstmt.executeQuery();				if(rs.next()){					strCategoryDatabase = rs.getString(1);				}				if (pstmt != null) {					pstmt.close();				}			}            //Sytem.out.println("Select category database name has been successfully retrieved");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(connection!=null){            	DBConnectionManager.freeKenanDbConnection(connection);            }            if(rs!=null){                rs=null;            }        }        return strCategoryDatabase;    }////////////////////////////////////////////////////////////////////////////////////////////////    public static String getCusDatabase(Connection connection,String categoryDatabase,String externalId)throws Exception {        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();		String strCusDatabase = "";        try{			if (connection!=null){				//search for status records first                query.append("SELECT DS_DATABASE FROM EXTERNAL_ID_ACCT_MAP@"+categoryDatabase+" EIAM,SERVER_DEFINITION@"+categoryDatabase+" SD WHERE EXTERNAL_ID='"+externalId+"' AND EXTERNAL_ID_TYPE=1 AND EIAM.SERVER_ID=SD.SERVER_ID");				// prepare the statement and bind parameter				pstmt = connection.prepareStatement(query.toString());                //Sytem.out.println("SQL string is....1"+query.toString());                rs   = pstmt.executeQuery();                //Sytem.out.println("SQL string is....4"+query.toString());				if(rs.next()){					strCusDatabase = rs.getString(1);				}				if (pstmt != null) {					pstmt.close();				}			}            //Sytem.out.println("Select customer database name with connection has been successfully retrieved");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }        }        return strCusDatabase;    }////////////////////////////////////////////////////////////////////////////////////////////////    public static String getCusDatabase(String categoryDatabase,String externalId)throws Exception {        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();		String strCusDatabase = "";        try{			connection = DBConnectionManager.getKenanDbConnection();			if (connection!=null){				//search for status records first                query.append("SELECT DS_DATABASE FROM EXTERNAL_ID_ACCT_MAP@"+categoryDatabase+" EIAM,SERVER_DEFINITION@"+categoryDatabase+" SD WHERE EXTERNAL_ID='"+externalId+"' AND EXTERNAL_ID_TYPE=1 AND EIAM.SERVER_ID=SD.SERVER_ID");				// prepare the statement and bind parameter				pstmt = connection.prepareStatement(query.toString());                //Sytem.out.println("SQL string is....2"+query.toString());                rs   = pstmt.executeQuery();                //Sytem.out.println("SQL string is....3"+query.toString());				if(rs.next()){					strCusDatabase = rs.getString(1);				}				if (pstmt != null) {					pstmt.close();				}			}            //Sytem.out.println("Select customer database name with connection has been successfully retrieved");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(connection!=null){            	DBConnectionManager.freeKenanDbConnection(connection);            }            if(rs!=null){                rs=null;            }        }        return strCusDatabase;    }////////////////////////////////////////////////////////////////////////////////////////////////    public static String getKenanAccountNo(Connection connection,String categoryDatabase,String externalId)throws Exception {        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();		String kenanAccountNo = "";        try{			if (connection!=null){				//search for status records first                query.append("SELECT ACCOUNT_NO FROM EXTERNAL_ID_ACCT_MAP@"+categoryDatabase+" WHERE EXTERNAL_ID_TYPE=1 AND EXTERNAL_ID='"+externalId+"'");				// prepare the statement and bind parameter				pstmt = connection.prepareStatement(query.toString());				//Sytem.out.println(query.toString());                rs   = pstmt.executeQuery();				if(rs.next()){					kenanAccountNo = rs.getString(1);				}				if (pstmt != null) {					pstmt.close();				}			}            //Sytem.out.println("Select kenan account no with connection has been successfully retrieved");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(rs!=null){                rs=null;            }        }        return kenanAccountNo;    }    public static String getKenanAccountNo(String categoryDatabase,String externalId)throws Exception {        Connection connection = null;        PreparedStatement pstmt = null;        ResultSet rs= null;        StringBuffer query = new StringBuffer();		String kenanAccountNo = "";		connection = DBConnectionManager.getKenanDbConnection();        try{			if (connection!=null){				//search for status records first                query.append("SELECT ACCOUNT_NO FROM EXTERNAL_ID_ACCT_MAP@"+categoryDatabase+" WHERE EXTERNAL_ID_TYPE=1 AND EXTERNAL_ID='"+externalId+"'");				// prepare the statement and bind parameter				pstmt = connection.prepareStatement(query.toString());				//Sytem.out.println(query.toString());                rs   = pstmt.executeQuery();				if(rs.next()){					kenanAccountNo = rs.getString(1);				}				if (pstmt != null) {					pstmt.close();				}			}            //Sytem.out.println("Select kenan account no with connection has been successfully retrieved");        }catch(Exception e){            throw new Exception("" + e);        }finally{            if(connection!=null){            	DBConnectionManager.freeKenanDbConnection(connection);            }            if(rs!=null){                rs=null;            }        }        return kenanAccountNo;    }///////////////////////////////////////////////////////////////////////////////////////////////	// /////////////////////////////////////////////////////////////////////////////////////////////	public static String processCUSPhysicalBillSubscription(String extId,			String cusDatabase, String categoryDatabase) throws Exception {		Connection connection = null;		PreparedStatement pstmt = null;		ResultSet rs = null;		StringBuffer query = new StringBuffer();		String strPhysicalBillSubscription = "";		int paramId = 19;		int paramDataType = 2;		try {			connection = DBConnectionManager.getKenanDbConnection();			if (connection != null) {				if (connection != null) {					// search for status records first					query.append("SELECT PARAM_VALUE FROM CMF_EXT_DATA@"							+ cusDatabase							+ " WHERE ACCOUNT_NO = (SELECT ACCOUNT_NO FROM CUSTOMER_ID_ACCT_MAP@"							+ cusDatabase							+ " WHERE EXTERNAL_ID_TYPE=1 AND EXTERNAL_ID='"							+ extId + "') AND PARAM_ID = " + paramId							+ " AND PARAM_DATATYPE = " + paramDataType + " ");					// prepare the statement and bind parameter					pstmt = connection.prepareStatement(query.toString());					//Sytem.out.println(query.toString());					rs = pstmt.executeQuery();					if (rs.next()) {						strPhysicalBillSubscription = rs.getString(1);					}					if (pstmt != null) {						pstmt.close();					}				}			}			System.out					.println("Process Physical Bill Subscription has been successfully retrieved");		} catch (Exception e) {			throw new Exception("" + e);		} finally {			if (connection != null) {				DBConnectionManager.freeKenanDbConnection(connection);			}			if (rs != null) {				rs = null;			}		}		return strPhysicalBillSubscription;	}	public static String processCUSEmailBillSubscription(Connection connection,			String cusDatabase, String extId, String categoryDatabase)			throws Exception {		PreparedStatement pstmt = null;		ResultSet rs = null;		StringBuffer query = new StringBuffer();		String strPhysicalBillSubscription = "";		int paramId = 17;		int paramDataType = 2;		try {			if (connection != null) {				// search for status records first				query.append("SELECT PARAM_VALUE FROM CMF_EXT_DATA@"						+ cusDatabase						+ " WHERE ACCOUNT_NO = (SELECT ACCOUNT_NO FROM CUSTOMER_ID_ACCT_MAP@"						+ cusDatabase						+ " WHERE EXTERNAL_ID_TYPE=1 AND EXTERNAL_ID='" + extId						+ "') AND PARAM_ID = " + paramId						+ " AND PARAM_DATATYPE = " + paramDataType + " ");				// prepare the statement and bind parameter				pstmt = connection.prepareStatement(query.toString());				rs = pstmt.executeQuery();				if (rs.next()) {					strPhysicalBillSubscription = rs.getString(1);				}				if (pstmt != null) {					pstmt.close();				}			}			System.out					.println("Process Physical Bill Subscription with connection has been successfully retrieved");		} catch (Exception e) {			throw new Exception("" + e);		} finally {			if (rs != null) {				rs = null;			}		}		return strPhysicalBillSubscription;	}	public static String processCUSPhysicalBillSubscription(			Connection connection, String cusDatabase, String extId,			String categoryDatabase) throws Exception {		PreparedStatement pstmt = null;		ResultSet rs = null;		StringBuffer query = new StringBuffer();		String strPhysicalBillSubscription = "";		int paramId = 19;		int paramDataType = 2;		try {			if (connection != null) {				// search for status records first				query.append("SELECT PARAM_VALUE FROM CMF_EXT_DATA@"						+ cusDatabase						+ " WHERE ACCOUNT_NO = (SELECT ACCOUNT_NO FROM CUSTOMER_ID_ACCT_MAP@"						+ cusDatabase						+ " WHERE EXTERNAL_ID_TYPE=1 AND EXTERNAL_ID='" + extId						+ "') AND PARAM_ID = " + paramId						+ " AND PARAM_DATATYPE = " + paramDataType + " ");				// prepare the statement and bind parameter				pstmt = connection.prepareStatement(query.toString());				rs = pstmt.executeQuery();				if (rs.next()) {					strPhysicalBillSubscription = rs.getString(1);				}				if (pstmt != null) {					pstmt.close();				}			}			System.out					.println("Process Physical Bill Subscription with connection has been successfully retrieved");		} catch (Exception e) {			throw new Exception("" + e);		} finally {			if (rs != null) {				rs = null;			}		}		return strPhysicalBillSubscription;	}	// /////////////////////////////////////////////////////////////////////////////////////////////	public static boolean checkIfPhysicalBillSubsExist(Connection connection,			String cusDatabase, String extId, String categoryDatabase)			throws Exception {		PreparedStatement pstmt = null;		ResultSet rs = null;		StringBuffer query = new StringBuffer();		int intResult = 0;		boolean boolResult = false;		int paramId = 19;		int paramDataType = 2;		try {			if (connection != null) {				if (connection != null) {					// search for status records first					query.append("SELECT COUNT(ACCOUNT_NO) FROM CMF_EXT_DATA@"							+ cusDatabase							+ " WHERE ACCOUNT_NO =  (SELECT ACCOUNT_NO FROM CUSTOMER_ID_ACCT_MAP@"							+ cusDatabase							+ " WHERE EXTERNAL_ID_TYPE=1 AND EXTERNAL_ID='"							+ extId + "') AND PARAM_ID =" + paramId							+ " AND PARAM_DATATYPE =" + paramDataType);					// prepare the statement and bind parameter					pstmt = connection.prepareStatement(query.toString());					//Sytem.out.println(query.toString());					rs = pstmt.executeQuery();					if (rs.next()) {						intResult = rs.getInt(1);					}					if (intResult != 0) {						boolResult = true;					}					if (pstmt != null) {						pstmt.close();					}				}			}			System.out					.println("Check if the record exist in CMF_EXT_DATA has successfully been executed");		} catch (Exception e) {			throw new Exception("" + e);		} finally {			// if(connection!=null){			// EbppxCusDBUtil.freeDbConnection(connection);			// }			if (rs != null) {				rs = null;			}		}		return boolResult;	}		 public static boolean checkIfEbillSubsExist(Connection connection,String cusDatabase,String extId,String categoryDatabase)throws Exception {	        PreparedStatement pstmt = null;	        ResultSet rs= null;	        StringBuffer query = new StringBuffer();	        int intResult = 0;			boolean boolResult = false;			int paramId = 17;			int paramDataType = 2;	        try{	            if (connection!=null){	                if (connection!=null){                         //Sytem.out.println("TEST 12");	                    //search for status records first	                    query.append("SELECT COUNT(ACCOUNT_NO) FROM CMF_EXT_DATA@"+cusDatabase+" WHERE ACCOUNT_NO =  (SELECT ACCOUNT_NO FROM CUSTOMER_ID_ACCT_MAP@"+cusDatabase+" WHERE EXTERNAL_ID_TYPE=1 AND EXTERNAL_ID='"+extId+"') AND PARAM_ID = " +paramId+" AND PARAM_DATATYPE = "+paramDataType);	                    // prepare the statement and bind parameter	                    pstmt = connection.prepareStatement(query.toString());				    	//Sytem.out.println(query.toString());	                    rs   = pstmt.executeQuery();						if(rs.next()){							intResult = rs.getInt(1);						}						if(intResult != 0){							boolResult = true;						}	                    if (pstmt != null) {	                        pstmt.close();	                    }	                }	            }	            //Sytem.out.println("Check if the record exist in CMF_EXT_DATA has successfully been executed");	        }catch(Exception e){	            throw new Exception("" + e);	        }finally{	            //if(connection!=null){	            //    EbppxCusDBUtil.freeDbConnection(connection);	            //}	            if(rs!=null){	                rs=null;	            }	        }	        return boolResult;	    }	 	  public static void insertInfoCustText(Connection connection, String cusDatabase,String activityCode, String acctId) throws Exception {	        try {				int intAcctId= Integer.parseInt(acctId);	            if (connection!=null){					StringBuffer stbQuery = new StringBuffer();	                StringBuffer stbSQL = new StringBuffer();			        ResultSet rs= null;	                String subscriptionStatus = "";	                int paramDataType = 2;	                int paramId = 0;	        		final DateFormat subsFormatter = new SimpleDateFormat("'Y,'dd/MM/yyyy");	        		final DateFormat unSubsFormatter = new SimpleDateFormat("'N,'dd/MM/yyyy");	            	Date dtUpdateDate = new Date();					if(activityCode.equals(EbppxConstants.NO_PHYSICAL_BILL_SUBSCRIPTION)){						// No physical bill subscription						subscriptionStatus = subsFormatter.format(dtUpdateDate);						paramId = 19;						stbSQL.append("INSERT INTO CMF_EXT_DATA@"+cusDatabase+"(ACCOUNT_NO,PARAM_ID,PARAM_VALUE,PARAM_DATATYPE) VALUES ("+intAcctId+","+paramId+",'"+subscriptionStatus+"',"+paramDataType+")");					}else if(activityCode.equals(EbppxConstants.NO_PHYSICAL_BILL_UNSUBSCRIPTION)){						// No physical bill unsubscription						subscriptionStatus = unSubsFormatter.format(dtUpdateDate);						paramId = 19;						stbSQL.append("INSERT INTO CMF_EXT_DATA@"+cusDatabase+"(ACCOUNT_NO,PARAM_ID,PARAM_VALUE,PARAM_DATATYPE) VALUES ("+intAcctId+","+paramId+",'"+subscriptionStatus+"',"+paramDataType+")");					}else if(activityCode.equals(EbppxConstants.EMAIL_DELIVERY_SUBSCRIPTION)){						// Bill Delivery email subscription						subscriptionStatus = subsFormatter.format(dtUpdateDate);						paramId = 17;						stbSQL.append("INSERT INTO CMF_EXT_DATA@"+cusDatabase+"(ACCOUNT_NO,PARAM_ID,PARAM_VALUE,PARAM_DATATYPE) VALUES ("+intAcctId+","+paramId+",'"+subscriptionStatus+"',"+paramDataType+")");					}else if(activityCode.equals(EbppxConstants.EMAIL_DELIVERY_UNSUBSCRIPTION)){						// Bill Delivery Email unsubscription						subscriptionStatus = unSubsFormatter.format(dtUpdateDate);						paramId = 17;						stbSQL.append("INSERT INTO CMF_EXT_DATA@"+cusDatabase+"(ACCOUNT_NO,PARAM_ID,PARAM_VALUE,PARAM_DATATYPE) VALUES ("+intAcctId+","+paramId+",'"+subscriptionStatus+"',"+paramDataType+")");					}					PreparedStatement pstmt = connection.prepareStatement(stbSQL.toString());					//Sytem.out.println(stbSQL.toString());					pstmt.executeUpdate();					if (pstmt != null) {						pstmt.close();					}	            }	        }	        catch(Exception e) {	            throw new Exception("Error inserting CMF_EXT_DATA : " + e);	        }	    }	  	  public static void updateInfoCustText(Connection connection,String cusDatabase, String activityCode, String acctId) throws Exception {	        try {	            if (connection!=null){	                StringBuffer stbSQL = new StringBuffer();	                String subscriptionStatus = "";	                int paramDataType = 2;	                int paramId = 0;	                int intAcctId = Integer.parseInt(acctId);	        		final DateFormat subsFormatter = new SimpleDateFormat("'Y,'dd/MM/yyyy");	        		final DateFormat unSubsFormatter = new SimpleDateFormat("'N,'dd/MM/yyyy");	            	Date dtUpdateDate = new Date();	            	if(activityCode.equals(EbppxConstants.NO_PHYSICAL_BILL_SUBSCRIPTION)){						// No physical bill subscription						subscriptionStatus = subsFormatter.format(dtUpdateDate);						paramId = 19;						stbSQL.append("UPDATE CMF_EXT_DATA@"+cusDatabase+" SET PARAM_VALUE = '"+subscriptionStatus+"' WHERE ACCOUNT_NO = "+intAcctId+" AND PARAM_ID = "+paramId+" AND PARAM_DATATYPE = "+paramDataType+" ");					}else if(activityCode.equals(EbppxConstants.NO_PHYSICAL_BILL_UNSUBSCRIPTION)){						// No physical bill unsubscription						subscriptionStatus = unSubsFormatter.format(dtUpdateDate);						paramId = 19;						stbSQL.append("UPDATE CMF_EXT_DATA@"+cusDatabase+" SET PARAM_VALUE = '"+subscriptionStatus+"' WHERE ACCOUNT_NO = "+intAcctId+" AND PARAM_ID = "+paramId+" AND PARAM_DATATYPE = "+paramDataType+" ");					}else if(activityCode.equals(EbppxConstants.EMAIL_DELIVERY_SUBSCRIPTION)){						// Bill Delivery email subscription						subscriptionStatus = subsFormatter.format(dtUpdateDate);						paramId = 17;						stbSQL.append("UPDATE CMF_EXT_DATA@"+cusDatabase+" SET PARAM_VALUE = '"+subscriptionStatus+"' WHERE ACCOUNT_NO = "+intAcctId+" AND PARAM_ID = "+paramId+" AND PARAM_DATATYPE = "+paramDataType+" ");					}else if(activityCode.equals(EbppxConstants.EMAIL_DELIVERY_UNSUBSCRIPTION)){						// Bill Delivery Email unsubscription						subscriptionStatus = unSubsFormatter.format(dtUpdateDate);						paramId = 17;						stbSQL.append("UPDATE CMF_EXT_DATA@"+cusDatabase+" SET PARAM_VALUE = '"+subscriptionStatus+"' WHERE ACCOUNT_NO = "+intAcctId+" AND PARAM_ID = "+paramId+" AND PARAM_DATATYPE = "+paramDataType+" ");					}					PreparedStatement pstmt = connection.prepareStatement(stbSQL.toString());					pstmt.executeUpdate();					if (pstmt != null) {						pstmt.close();					}	            }	        }	        catch(Exception e) {	            throw new Exception("Error updating CMF_EXT_DATA : " + e);	        }	    }	  	  public static void logActivity(Connection connection,Statement stmt, String activityCode, String acctId, String userId, String role, String status, String date1, String date2, String userField1, String userField2, String userField3, String userField4) throws Exception {	        try {	            if (connection!=null){	                StringBuffer stbSQL = new StringBuffer();	                final DateFormat df = new SimpleDateFormat(EbppxConstants.DATE_FORMAT+" "+EbppxConstants.TIME_FORMAT);	                java.sql.Timestamp ts1 = null;	                java.sql.Timestamp ts2 = null;	                if (!date1.equals("")){	                    ts1 = new Timestamp((df.parse(date1)).getTime());	                }	                if (!date2.equals("")){	                    ts2 = new Timestamp((df.parse(date2)).getTime());	                }	                PreparedStatement pstmt = connection.prepareStatement("INSERT INTO CUSTLOG (activityCode, acctId, userId, role, status, date1, date2, userField1, userField2, userField3, userField4) VALUES (?,?,?,?,?,?,?,?,?,?,?)");	                pstmt.setString(1,activityCode);	                pstmt.setString(2,acctId);	                pstmt.setString(3,userId);	                pstmt.setString(4,role);	                pstmt.setString(5,status);	                pstmt.setTimestamp(6,ts1);	                pstmt.setTimestamp(7,ts2);	                pstmt.setString(8,userField1);	                pstmt.setString(9,userField2);	                pstmt.setString(10,userField3);	                pstmt.setString(11,userField4);	                pstmt.execute();	                if (pstmt != null) {	                    pstmt.close();	                }	            }	        }	        catch(Exception e) {	            throw new Exception("Error logging user activity : " + e);	        }	    }	  	    public static void logUserActivity(String activityCode, String acctId, String userId, String role, String status, String date1, String date2, String userField1, String userField2, String userField3, String userField4) throws Exception {	        Connection connection = null;	        Statement stmt = null;	        try {	            connection = DBConnectionManager.getDbConnection();	            if (connection!=null){	                stmt = connection.createStatement();	                if (stmt!=null){	                    connection.setAutoCommit(false);	                    logActivity(connection, stmt, activityCode, acctId, userId, role, status, date1, date2, userField1, userField2, userField3, userField4);	                    connection.commit();	                    connection.setAutoCommit(true);	                    	                    DBConnectionManager.freeDbConnection(connection);	                }	                //Sytem.out.println("Transaction has been committed");	            }	        }	        catch(Exception e) {	            throw new Exception("" + e);	        }finally {	            if (stmt!=null){	                stmt.close();	            }	            if (connection!=null){	                DBConnectionManager.freeDbConnection(connection);	            }	        }	    }	    	    //=================added by tzer kang	   	    public static EbppxAcctDetailsDataBean getLatestEmailDeliveryStatus(Connection connection,String acctId) throws SQLException{	    	 	    	ResultSet rs= null;	    	EbppxAcctDetailsDataBean acctDataBean = new EbppxAcctDetailsDataBean();	    	String registerStatus ="";	    	String emailAddress ="";	    	String emailStatus = "";	    	String channelID = "";	    	String userID = "";	    	Date registerDate = null;	    	PreparedStatement pstmt = null;	      	    	List status =new ArrayList();	 	    	try{	    		if(connection != null){	    				    			   pstmt = connection.prepareStatement("SELECT TRIM(ACCTID),TRIM(EMAILADDR),DATE1,TRIM(USERID),TRIM(USERFIELD1), TRIM(USERFIELD2),TRIM(CHANNELID) FROM EBILLREGISTERLOG WHERE ACCTID=? ORDER BY DATE1 DESC ");	    			   	                   	                   pstmt.setString(1,acctId);	                   pstmt.setMaxRows(1);	                   rs   = pstmt.executeQuery();	                   while(rs.next()){	                	   					    registerStatus = rs.getString(5);					    emailAddress =rs.getString(2);					    emailStatus = rs.getString(6);					    registerDate = rs.getTimestamp(3);					    userID = rs.getString(4);					    channelID = rs.getString(7);	 			        //Sytem.out.println("registerStatus :"+registerStatus);					    acctDataBean.setEmailAdd(emailAddress);					    acctDataBean.setEmailStatus(registerStatus);					    acctDataBean.setRegisterDate(registerDate);					    acctDataBean.setLastModifyDate(registerDate);					    acctDataBean.setUpdatedByChannel(channelID);					    acctDataBean.setUpdatedByUser(userID);								    	                   }					    							   //Sytem.out.println("registerDate :"+acctDataBean.getRegisterDate());					    					    										 //Sytem.out.println("registerStatus :"+registerStatus);					 //Sytem.out.println("Email Address :"+acctDataBean.getEmailAdd());							                   if (pstmt != null) {	                       pstmt.close();	                   }	                   if (rs != null) {	                       rs.close();	                   }	                   	                }	    		}catch(Exception e){	            	   e.printStackTrace();	    		}finally{	    		 	    			if(pstmt != null){	    										pstmt.close();						    			}	    			if(rs != null){	    				rs.close();	    			}	    		}	    		return acctDataBean;	 }	  	   public static void updateRegisterDate(Connection connection, String acctId,String strRegisterDate) throws Exception {	        try {	        	final DateFormat df = new SimpleDateFormat(EbppxConstants.DATE_FORMAT+" "+EbppxConstants.TIME_FORMAT);	        	java.sql.Timestamp registerDate = null;	        	 	            if(connection!=null){	            		            		                if (!strRegisterDate.equals("")){	                	registerDate = new Timestamp((df.parse(strRegisterDate)).getTime());	                }	                	                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET REGISTER_DATE = ? WHERE ACCTID = ?");	                pstmt.setTimestamp(1,registerDate);	                pstmt.setString(2,acctId);	                pstmt.executeUpdate();					if (pstmt != null) {						pstmt.close();					}	            }	        }	        catch(Exception e) {	            throw new Exception("Error logging updating account details table for RegisterDate: " + e);	        }	    }	  	   public static void updateAcctInfo(Connection connection,String acctId,String lastModifiedDate ,String updatedByChannel,String updatedByUser) throws Exception {	        try {	        	final DateFormat df = new SimpleDateFormat(EbppxConstants.DATE_FORMAT+" "+EbppxConstants.TIME_FORMAT);	        	java.sql.Timestamp modifyDate = null;	        		            if(connection!=null){	                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET LASTMODIFYDATE = ? ,UPDATEDBYCHANNEL = ?,UPDATEDBYUSER = ? WHERE ACCTID = ?");	               	               	                if (!lastModifiedDate.equals("")){	                	modifyDate = new Timestamp((df.parse(lastModifiedDate)).getTime());	                }	                pstmt.setTimestamp(1,modifyDate);	                pstmt.setString(2, updatedByChannel);	                pstmt.setString(3,updatedByUser);	                pstmt.setString(4,acctId);	                pstmt.executeUpdate();					if (pstmt != null) {						pstmt.close();					}	            }	        }	        catch(Exception e) {	            throw new Exception("Error logging updating account details table for acctDetails: " + e);	        }	    }	    			    		   public static void updateAcctInfo(Connection connection,String acctId,String emailSub ,String registerDate,String lastModifiedDate ,String updatedByChannel,String updatedByUser) throws Exception {	        try {	        	final DateFormat df = new SimpleDateFormat(EbppxConstants.DATE_FORMAT+" "+EbppxConstants.TIME_FORMAT);	        	java.sql.Timestamp modifyDate = null;	        	java.sql.Timestamp registerDateF = null;	        		            if(connection!=null){	                PreparedStatement pstmt = connection.prepareStatement("UPDATE ACCOUNTDETAILS SET EMAILDELIVERYSUBSCRIPTION = ?,REGISTER_DATE = ? ,LASTMODIFYDATE = ? ,UPDATEDBYCHANNEL = ?,UPDATEDBYUSER = ? WHERE ACCTID = ?");	               	               	               if (!lastModifiedDate.equals("")){	                	modifyDate = new Timestamp((df.parse(lastModifiedDate)).getTime());	                }	                if(!registerDate.equals("")){	                		                	registerDateF = new Timestamp((df.parse(registerDate)).getTime());	                }	                //pstmt.setString(1,emailAddr);	                	                pstmt.setString(1,emailSub);	                pstmt.setTimestamp(2,modifyDate);	                pstmt.setTimestamp(3,registerDateF);	                pstmt.setString(4,updatedByChannel);	                pstmt.setString(5,updatedByUser);	                pstmt.setString(6,acctId);	                pstmt.executeUpdate();					if (pstmt != null) {						pstmt.close();					}	            }	        }	        catch(Exception e) {	            throw new Exception("Error logging updating account details table for acctDetails: " + e);	        }	    }}